﻿package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Student;


public class StudentControl {
	/**
	 * 修改学生密码
	 * @param id
	 * @param pwd
	 * @return
	 * @throws SQLException
	 */
	public boolean updatePassword(int id, String pwd) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql = "{call updateStuPassword("+ id +", '"+ pwd +"')}";
		//String sql = "UPDATE student SET stu_pwd = '"+ pwd +"' WHERE stu_id = "+ id +" ";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
		//	dbc.executeUpdate(sql);
			return true;
		} finally {
			dbc.close();
		}
	}
	/**插入学生
	 * @param student
	 * @return
	 * @throws SQLException
	 */
	//stu_birth may cause error
	public boolean insertStu(Student student, boolean flag){
		DBConnect dbc = new DBConnect();
		/*
		String sql1 = "Insert into student(stu_num, stu_pwd, stu_name, stu_sex, stu_tel, stu_birth, stu_college, stu_major, stu_dorm) " +
				"values('"+ student.getStu_num() +"', '"+ student.getStu_pwd() +"', '"+ student.getStu_name() +"', " +
						"'"+ student.getStu_sex() +"', '"+ student.getStu_tel() +"', '"+ student.getStu_birth() +"', " +
								"'"+ student.getStu_college() +"', '"+ student.getStu_major() +"', "+ student.getStu_dorm_id() +")";
		String sql2 = "update dormitory set dorm_leader = (select max(stu_id) from student) where " +
				"dorm_id = "+ student.getStu_dorm_id() +"";
		
		String sql = "{call updateStu('"+ student.getStu_num() +"', '"+ student.getStu_pwd() +"', '"+ student.getStu_name() +"', " +
		"'"+ student.getStu_sex() +"', '"+ student.getStu_tel() +"', " +
		"'"+ student.getStu_college() +"', " +
		"'"+ student.getStu_major() +"', " +
		""+ student.getStu_dorm_id() +", "+ flag +")}";*/
		String sql = "{call insertStu(?,?,?,?,?,?,?,?,?,?)}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.setString(1, student.getStu_num());
			stmt.setString(2, student.getStu_pwd());
			stmt.setString(3, student.getStu_name());
			stmt.setString(4, student.getStu_sex());
			stmt.setString(5, student.getStu_tel());
			stmt.setString(6, student.getStu_birth());
			stmt.setString(7, student.getStu_college());
			stmt.setString(8, student.getStu_major());
			stmt.setInt(9, student.getStu_dorm_id());
			stmt.setBoolean(10, flag);
			stmt.execute();
			//dbc.executeUpdate(sql1);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入student失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}
	/**删除学生
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteStu(int stu_id) throws SQLException {
		DBConnect dbc = new DBConnect();
		/*
		String sql1 = "Delete from comment where stu_id = "+ id +"";
		String sql2 = "Update dormitory set dorm_leader = NULL where dorm_id = (select dorm_id from student,dormitory " +
																				"where dormitory.dorm_leader = student.stu_id " +
																				"and student.stu_id = "+ id +")";
		String sql3 = "Delete from fault where stu_id = "+ id +"";
		String sql4 = "Delete from  student where stu_id = "+ id +" ";
		*/
		String sql = "{call deleteStu("+ stu_id +")}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除stu失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}
	/**
	 * 更新学生信息
	 * @param student
	 * @param flag
	 * @return
	 * @throws SQLException
	 */
	public boolean updateStu(Student student, boolean flag) throws SQLException{
		DBConnect dbc = new DBConnect();
		/*
		String sql1 = "update student set stu_pwd = '"+ student.getStu_pwd() +"', " +
					"stu_name = '"+ student.getStu_name() +"', " +
					"stu_sex = '"+ student.getStu_sex() +"', " +
					"stu_tel = '"+ student.getStu_tel() +"', " +
					"stu_birth = '"+ student.getStu_birth() +"', " +
					"stu_college = '"+ student.getStu_college() +"', " +
					"stu_major = '"+ student.getStu_major() +"', " +
					"stu_dorm = "+ student.getStu_dorm_id() +" " +
					"where stu_id = "+ student.getStu_id() +" ";
		String sql2 = "update dormitory set dorm_leader = "+ student.getStu_id() +" where " +
					"dorm_id = "+ student.getStu_dorm_id() +"";
		
		String sql = "{call updateStu('"+ student.getStu_pwd() +"', '"+ student.getStu_name() +"', " +
				"'"+ student.getStu_sex() +"', '"+ student.getStu_tel() +"', " +
				"'"+ student.getStu_birth() +"', '"+ student.getStu_college() +"', " +
				"'"+ student.getStu_major() +"', "+ student.getStu_dorm_id() +" " +
				""+ student.getStu_id() +", "+ flag +")}";*/
		String sql = "{call updateStu(?,?,?,?,?,?,?,?,?,?)}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.setString(1, student.getStu_pwd());
			stmt.setString(2, student.getStu_name());
			stmt.setString(3, student.getStu_sex());
			stmt.setString(4, student.getStu_tel());
			stmt.setString(5, student.getStu_birth());
			stmt.setString(6, student.getStu_college());
			stmt.setString(7, student.getStu_major());
			stmt.setInt(8, student.getStu_dorm_id());
			stmt.setInt(9, student.getStu_id());
			stmt.setBoolean(10, flag);
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("更新stu失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}
	
	/*public ArrayList<Student> getStuInformation(int stu_dorm_id) {
		ArrayList<Student> studentlist = new ArrayList<Student>();
		DBConnect dbc = new DBConnect();
		if(stu_dorm_id == 0) {
			String sql = "select * from student,dormitory " +
					"where student.stu_dorm = dormitory.dorm_id";
			try {
				ResultSet rs = dbc.executeQuery(sql);
				while(rs.next()) {
					Student student = new Student();
					student.setStu_id(rs.getInt("admin_id"));
					student.setStu_num(rs.getString("admin_num"));
					student.setStu_pwd(rs.getString("admin_pwd"));
					student.setStu_name(rs.getString("admin_name"));
					student.setStu_sex(rs.getString("admin_sex"));
					student.setStu_tel(rs.getString("admin_tel"));
					student.setStu_building_id(rs.getInt("building_id"));
					student.setStu_building_name(rs.getString("building_name"));
					studentlist.add(student);
				}
				return adminlist;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				dbc.closeStmt();
				dbc.closeConn();
			}
		}
		else {
			String sql = "select * from admin,building " +
					"where admin.admin_building = building.building_id " +
					"and building.building_id = "+ building_id +" ";
			try {
				ResultSet rs = dbc.executeQuery(sql);
				while(rs.next()) {
					Admin admin = new Admin();
					admin.setAdmin_id(rs.getInt("admin_id"));
					admin.setAdmin_num(rs.getString("admin_num"));
					admin.setAdmin_pwd(rs.getString("admin_pwd"));
					admin.setAdmin_name(rs.getString("admin_name"));
					admin.setAdmin_sex(rs.getString("admin_sex"));
					admin.setAdmin_tel(rs.getString("admin_tel"));
					admin.setAdmin_building_id(rs.getInt("building_id"));
					admin.setAdmin_building_name(rs.getString("building_name"));
					adminlist.add(admin);
				}
				return adminlist;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				dbc.closeStmt();
				dbc.closeConn();
			}
		}
	}*/
	
	/**
	 * 根据学生姓名，寝室楼id，寝室名查询学生信息
	 */
	public ArrayList<Student> getStuInformation(String stu_name, int stu_building_id, String stu_dorm_name) {
		ArrayList<Student> studentlist = new ArrayList<Student>();
		DBConnect dbc = new DBConnect();
		String sql = "{call getStuInformation('"+ stu_name +"', "+ stu_building_id +", '"+ stu_dorm_name +"')}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Student stu = new Student();
				stu.setStu_id(rs.getInt("stu_id"));
				stu.setStu_num(rs.getString("stu_num"));
				stu.setStu_name(rs.getString("stu_name"));
				stu.setStu_sex(rs.getString("stu_sex"));		
				String str = rs.getString("stu_birth");
				if(str != null) {
					stu.setStu_birth((rs.getString("stu_birth")).substring(0, 10));
				}
				stu.setStu_tel(rs.getString("stu_tel"));
				stu.setStu_college(rs.getString("stu_college"));
				stu.setStu_major(rs.getString("stu_major"));
				stu.setStu_building_name(rs.getString("building_name"));
				stu.setStu_dorm_name(rs.getString("dorm_name"));
				studentlist.add(stu);
			}
			return studentlist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	/**
	 * 根据学生id获取学生信息
	 * @param stu_id
	 * @return
	 */
	public Student getStudent(int stu_id) {
		DBConnect dbc = new DBConnect();
		/*String sql="select * from student,dormitory,building where "+
		"student.stu_dorm = dormitory.dorm_id " +
		"and dormitory.dorm_building = building.building_id " +
		"and student.stu_id="+stu_id+"";*/
		String sql = "{call getStudent("+ stu_id +")}";
		
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			//ResultSet rs=dbc.executeQuery(sql);
			Student stu=new Student();
			while(rs.next()){
				stu.setStu_id(rs.getInt("stu_id"));
				stu.setStu_pwd(rs.getString("stu_pwd"));
				stu.setStu_num(rs.getString("stu_num"));
				stu.setStu_name(rs.getString("stu_name"));
				stu.setStu_sex(rs.getString("stu_sex"));
				String str = rs.getString("stu_birth");
				if(str != null) {
					stu.setStu_birth((rs.getString("stu_birth")).substring(0, 10));
				}
				stu.setStu_tel(rs.getString("stu_tel"));
				stu.setStu_college(rs.getString("stu_college"));
				stu.setStu_major(rs.getString("stu_major"));
				stu.setStu_building_name(rs.getString("building_name"));
				stu.setStu_building_id(rs.getInt("building_id"));
				stu.setStu_dorm_name(rs.getString("dorm_name"));
			}
			return stu;
			}
		catch(SQLException e){
			e.getStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
}

